package com.jack.bookshop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jack.bookshop.util.DatabaseUtil;
import com.jack.bookshop.vo.BookVo;
import com.jack.bookshop.vo.PageVo;

public class BookDao {
    //图书搜索


    // 随机获取n本图书
    public List<BookVo> getListByRandom(int n){
        List<BookVo> list = new ArrayList<BookVo>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql = "select * from book order by rand() limit " + n;
        try {
            conn = DatabaseUtil.getConnection();
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                BookVo book = new BookVo();
                book.setId(rs.getInt("id"));
                book.setTitle(rs.getString("title"));
                book.setImage_url(rs.getString("image_url"));
                //book.setImage_url("assets/images/books/" + rs.getString("image_url"));
                book.setC1_id(rs.getInt("c1_id"));
                book.setC1_text(rs.getString("c1_text"));
                book.setC2_id(rs.getInt("c2_id"));
                book.setC2_text(rs.getString("c2_text"));
                book.setPrice(rs.getBigDecimal("price"));
                book.setDiscount(rs.getFloat("discount"));
                book.setScore(rs.getInt("score"));
                book.setStock(rs.getInt("stock"));
                // 图书列表页一般不需要显示图书详情
                // book.setDescription(rs.getString("description"));
                list.add(book);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        return list;
    }

    // 根据id获得1本图书
    public BookVo getOneById(int id) {
        BookVo book = new BookVo();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            String sql = "select title,image_url,c1_id,c1_text,c2_id,c2_text,description,price,score from book where id=?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            if (rs.next()) {
                book = new BookVo();
                book.setId(id);
                book.setTitle(rs.getString("title"));
                book.setImage_url(rs.getString("image_url"));
                book.setC1_id(rs.getInt("c1_id"));
                book.setC1_text(rs.getString("c1_text"));
                book.setC2_id(rs.getInt("c2_id"));
                book.setC2_text(rs.getString("c2_text"));
                book.setDescription(rs.getString("description"));
                book.setPrice(rs.getBigDecimal("price"));
                book.setScore(rs.getInt("score"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        return book;
    }

    public PageVo<BookVo> getListByC2Id(int c2Id, int page, int size) {
        PageVo<BookVo> pageVo = new PageVo<BookVo>();
        double total = 0;// 总页数
        List<BookVo> list = new ArrayList<BookVo>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            // 获取总页数
            String sql1 = "select count(*) as total from book where c2_id=? ";
            pst = conn.prepareStatement(sql1);
            pst.setInt(1, c2Id);
            rs = pst.executeQuery();
            if(rs.next()) {
                // 设置总页数
                total = rs.getInt("total");
                total = Math.ceil(total/size);
//                total = total % size == 0 ? total / size : total / size + 1;
            }
            rs.close();
            pst.close();

            // 获取当前页数据
            String sql2 = "select id,title,image_url,c1_id,c1_text,c2_id,c2_text,price,score,description from book where c2_id=? limit ?,?; ";
            pst = conn.prepareStatement(sql2);
            pst.setInt(1, c2Id);
            pst.setInt(2, (page - 1) * size);
            pst.setInt(3, size);
            rs = pst.executeQuery();
            while (rs.next()) {
                BookVo book = new BookVo();
                book.setId(rs.getInt("id"));
                book.setTitle(rs.getString("title"));
                book.setImage_url(rs.getString("image_url"));
                book.setC1_id(rs.getInt("c1_id"));
                book.setC1_text(rs.getString("c1_text"));
                book.setC2_id(rs.getInt("c2_id"));
                book.setC2_text(rs.getString("c2_text"));
                book.setPrice(rs.getBigDecimal("price"));
                book.setScore(rs.getInt("score"));
                book.setDescription(rs.getString("description"));
                list.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        pageVo.setPage(page);
        pageVo.setSize(size);
        pageVo.setList(list);
        pageVo.setTotal((int)total);
        return pageVo;
    }

    public PageVo<BookVo> getListBySearch(String word, int page, int size) {
        PageVo<BookVo> pageVo = new PageVo<BookVo>();
        int total = 1;// 总页数
        List<BookVo> list = new ArrayList<BookVo>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            // 获取总记录数
            String sql = "select count(*) as total from book where title like ? or description like ? ";
            pst = conn.prepareStatement(sql);
            pst.setString(1, "%" + word + "%");
            pst.setString(2, "%" + word + "%");
            rs = pst.executeQuery();
            if (rs.next()) {
                // 总记录数
                total = rs.getInt("total");
                // 总页数 = 总记录数/每页大小 + 1
                total = total % size == 0 ? total / size : total / size + 1;
            }
            rs.close();
            pst.close();

            // 获取当前页数据
            String sql2 = "select id,title,image_url,c1_id,c1_text,c2_id,c2_text,price,score from book where title like ? or description like ? limit ?,?; ";
            pst = conn.prepareStatement(sql2);
            pst.setString(1, "%" + word + "%");
            pst.setString(2, "%" + word + "%");
            pst.setInt(3, (page - 1) * size);
            pst.setInt(4, size);
            rs = pst.executeQuery();
            while (rs.next()) {
                BookVo book = new BookVo();
                book.setId(rs.getInt("id"));
                book.setTitle(rs.getString("title"));
                book.setImage_url(rs.getString("image_url"));
                book.setC1_id(rs.getInt("c1_id"));
                book.setC1_text(rs.getString("c1_text"));
                book.setC2_id(rs.getInt("c2_id"));
                book.setC2_text(rs.getString("c2_text"));
                book.setPrice(rs.getBigDecimal("price"));
                book.setScore(rs.getInt("score"));
                list.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        pageVo.setPage(page);
        pageVo.setSize(size);
        pageVo.setList(list);
        pageVo.setTotal(total);
        return pageVo;
    }

    // 基于全文索引的搜索
    public PageVo<BookVo> getListByFullText(String word, int page, int size) {
        PageVo<BookVo> pageVo = new PageVo<BookVo>();
        int total = 1;// 总页数
        List<BookVo> list = new ArrayList<BookVo>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = DatabaseUtil.getConnection();
            // 获取总记录数
            String sql = "select count(*) as total from book where match(title,description) against(?)";
            pst = conn.prepareStatement(sql);
            pst.setString(1, word);
            rs = pst.executeQuery();
            if (rs.next()) {
                // 总记录数
                total = rs.getInt("total");
                // 总页数 = 总记录数/每页大小 + 1
                total = total % size == 0 ? total / size : total / size + 1;
            }
            rs.close();
            pst.close();

            // 获取当前页数据
            String sql2 = "select id,title,image_url,c1_id,c1_text,c2_id,c2_text,price,score,description from book where match(title,description) against(?) limit ?,?; ";
            pst = conn.prepareStatement(sql2);
            pst.setString(1, word);
            pst.setInt(2, (page - 1) * size);
            pst.setInt(3, size);
            rs = pst.executeQuery();
            while (rs.next()) {
                BookVo book = new BookVo();
                book.setId(rs.getInt("id"));
                book.setTitle(rs.getString("title"));
                book.setImage_url(rs.getString("image_url"));
                book.setC1_id(rs.getInt("c1_id"));
                book.setC1_text(rs.getString("c1_text"));
                book.setC2_id(rs.getInt("c2_id"));
                book.setC2_text(rs.getString("c2_text"));
                book.setPrice(rs.getBigDecimal("price"));
                book.setScore(rs.getInt("score"));
                book.setDescription(rs.getString("description"));
                list.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeAll(rs, pst, conn);
        }
        pageVo.setPage(page);
        pageVo.setSize(size);
        pageVo.setList(list);
        pageVo.setTotal(total);
        return pageVo;
    }


    public static void main(String[] args) {
        PageVo<BookVo> pageVo = new BookDao().getListByC2Id(14, 2, 10);
        System.out.println(pageVo);
        List<BookVo> list = pageVo.getList();
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).getTitle());
        }
    }
}

